#!/usr/bin/env python
# -*- coding:utf-8 -*-
"""
Author : gdlmo
MailTo : 66565551@qq.com
Create : 2017-08-09
Version: 1.0
----------------------------------------------------------------------------------------
DB-API 2.0 interface for SQLite databases
    SQLite数据库是一款非常小巧的嵌入式开源数据库软件，也就是说没有独立的维护进程，所有的维护都来自于程序本身。

    在python中，使用sqlite3创建数据库的连接，当我们指定的数据库文件不存在的时候连接对象会自动创建数据库文件；
如果数据库文件已经存在，则连接对象不会再创建数据库文件，而是直接打开该数据库文件。
    连接对象可以是硬盘上面的数据库文件，也可以是建立在内存中的，在内存中的数据库执行完任何操作后，都不需要提交事务的(commit)

    创建在硬盘上面： conn = sqlite3.connect('c:\\test\\test.db')
    创建在内存上面： conn = sqlite3.connect('"memory:')

    在注释中将会使用student表为例子，student表结构如下：
    CREATE TABLE `student` (
                          `id` int(11) NOT NULL,
                          `name` varchar(20) NOT NULL,
                          `gender` varchar(4) DEFAULT NULL,
                          `age` int(11) DEFAULT NULL,
                          `address` varchar(200) DEFAULT NULL,
                          `phone` varchar(20) DEFAULT NULL,
                           PRIMARY KEY (`id`)
                        )
"""
import sqlite3

#region global var
#是否打印sql
SHOW_SQL = True
#endregion

# region 创建|删除表操作
def dropTable(dbFilename, table):
    '''
    如果表存在,则删除表，如果表中存在数据的时候，使用该方法的时候要慎用！
    调用如下：
        dropTable(DB_FILE_PATH, 'student')
    '''
    if table is not None and table != '':
        sql = 'DROP TABLE IF EXISTS ' + table
        if SHOW_SQL:
            print('执行sql:[{}]'.format(sql))
        with sqlite3.connect(dbFilename) as conn:
            conn.execute(sql)
            conn.commit()
            print('删除数据库表[{}]成功!'.format(table))
    else:
        print('the [{}] is empty or equal None!'.format(sql))

def createTable(dbFilename, sql):
    '''
    创建数据库表：student
    调用如下：
        create_table_sql = ""CREATE TABLE `student` (`id` int(11) NOT NULL,`name` varchar(20) NOT NULL,.....,PRIMARY KEY (`id`))
        createTable(DB_FILE_PATH), create_table_sql)
    '''
    if sql is not None and sql != '':
        if SHOW_SQL:
            print('执行sql:[{}]'.format(sql))
        with sqlite3.connect(dbFilename) as conn:
            conn.execute(sql)
            conn.commit()
    else:
        print('the [{}] is empty or equal None!'.format(sql))
#endregion 创建|删除表操作     END

#region 数据库操作CRUD
def save(dbFilename, sql, params):
    '''插入数据
    使用方法：
        save_sql = 'INSERT INTO student values (?, ?, ?, ?, ?, ?)'
        data = [(1, 'Hongten', '男', 20, '广东省广州市', '13423****62'),
            (2, 'Tom', '男', 22, '美国旧金山', '15423****63'),
            (3, 'Jake', '女', 18, '广东省广州市', '18823****87'),
            (4, 'Cate', '女', 21, '广东省广州市', '14323****32')]
        save(DB_FILE_PATH, save_sql, data)
    '''
    if sql is not None and sql != '':
        if params is not None:
            with sqlite3.connect(dbFilename) as conn:
                cursor = conn.cursor()
                if SHOW_SQL:
                    for p in params:
                        print('执行sql:[{}],参数:[{}]'.format(sql, p))
                        cursor.execute(sql, p)
                conn.commit()
    else:
        print('the [{}] is empty or equal None!'.format(sql))

def fetchall(dbFilename, sql, *params):
    '''查询所有数据
    使用方法：
        fetchall_sql = "SELECT * FROM student where id=?"
        rows = fetchall(DB_FILE_PATH, fetchall_sql，（1,))
        for r in rows:
            id, name, gender, age, address, phone = r
            print('%s, %s, %s, %s, %s, %s' % (id, name, gender, age, address, phone))
    注意：如果没有params时，使用的方法如下：
        fetchall_sql = "SELECT * FROM student where 1=?"
        rows = fetchall(DB_FILE_PATH, fetchall_sql，（1,))
        for r in rows:
            id, name, gender, age, address, phone = r
            print('%s, %s, %s, %s, %s, %s' % (id, name, gender, age, address, phone))
    '''
    if sql is not None and sql != '':
        if SHOW_SQL:
            print('执行sql:[{}],参数:[{}]'.format(sql, params))
        with sqlite3.connect(dbFilename) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute(sql, *params)
            rows = cursor.fetchall()
            return rows
    else:
        print('the sql : [{}] and params: [{}]is empty or equal None!'.format(sql, params))

def update(dbFilename, sql, params):
    '''更新数据
    使用方法：
        update_sql = 'UPDATE student SET name = ? WHERE ID = ? '
        data = [('HongtenAA', 1),
            ('HongtenBB', 2),
            ('HongtenCC', 3),
            ('HongtenDD', 4)]
        update(DB_FILE_PATH, update_sql, data)
    '''
    if sql is not None and sql != '':
        if params is not None:
            with sqlite3.connect(dbFilename) as conn:
                cursor = conn.cursor()
                for p in params:
                    if SHOW_SQL:
                        print('执行sql:[{}],参数:[{}]'.format(sql, p))
                    cursor.execute(sql, p)
                conn.commit()
        else:
            print('参数不正确')
    else:
        print('the [{}] is empty or equal None!'.format(sql))

def delete(dbFilename, sql, params):
    '''删除数据
    使用方法：
        delete_sql = 'DELETE FROM student WHERE NAME = ? AND ID = ? '
        data = [('HongtenAA', 1),
                ('HongtenCC', 3)]
        delete(DB_FILE_PATH, delete_sql, data)
    '''
    if sql is not None and sql != '':
        if params is not None:
            with sqlite3.connect(dbFilename) as conn:
                cursor = conn.cursor()
                for p in params:
                    if SHOW_SQL:
                        print('执行sql:[{}],参数:[{}]'.format(sql, p))
                    cursor.execute(sql, p)
                conn.commit()
        else:
            print('参数不正确')
    else:
        print('the [{}] is empty or equal None!'.format(sql))
#endregion 数据库操作CRUD


